package org.yf.samples.dao;


import java.util.List;

import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.yf.samples.model.Subject;

@Repository
@Transactional
public class SubjectDAOImpl extends BaseHibernateDAO<Subject> implements ISubjectDAO {

	@Override
	public List<Subject> FindAllSubjects() {
		return this.find("FROM Subject");
	}
	
	@Override
	public List<Subject> FindSubjectsByUserId(String userId) {
		String hql = " SELECT s FROM Subject AS s, UserSubject AS us WHERE s.subId=us.subject.subId AND us.user.userId=? ";
		return this.queryForList(hql, new Object[]{userId});
	}

	@Override
	public Subject getSubjectById(Integer subId) {
		return this.get(subId);
	}

	@Override
	public void createSubject(Subject subject) throws Exception {
		this.save(subject);
	}

	@Override
	public void editSubject(Subject subject) throws Exception {
		this.update(subject);
	}

	@Override
	public void deleteSubject(Integer subId) throws Exception {
		Subject exsitsSubject = this.get(subId);
		if(exsitsSubject != null){
			this.delete(exsitsSubject);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllUserSubject(Integer subId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select user.user_id as userId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where es.sub_id = :subId ");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("userId", StringType.INSTANCE)
				.setInteger("subId", subId)
				.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getSubjectsByUserId(String userId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select eus.sub_id as subId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" where eus.user_id = :userId ");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("subId", IntegerType.INSTANCE)
				.setString("userId", userId)
				.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getSubjectsByTest(String userId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select es.subject_id as subId ");
		sql.append(" from exam_score es ");
		sql.append(" where es.user_id = :userId and score > 0 ");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("subId", IntegerType.INSTANCE)
				.setString("userId", userId)
				.list();
	}

	
	/*
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	private static final String FIND_ALL_SUBJECT = " SELECT sub_id,name,description,test_count,total_time,total_score,status FROM exam_subject ";
	
	private static final String FIND_BY_ID = " SELECT * FROM exam_subject WHERE sub_id=? ";
	
	private static final String FIND_CREATE_SUBJECT = " INSERT INTO exam_subject VALUES (?,?,?,?,?,?,?) ";
	
	private static final String FIND_EDIT_SUBJECT = " UPDATE exam_subject SET sub_id=?,name=?,description=?,test_count=?,total_time=?,total_score=?,status=? WHERE sub_id=? ";
	
	private static final String FIND_DELETE_SUBJECT = " DELETE FROM exam_subject WHERE sub_id=? ";


	public static final class SubjectMapper implements RowMapper<Subject>{
		public Subject mapRow(ResultSet rs, int rowNum) throws SQLException {
			Subject subject = new Subject();
			subject.setSubId(rs.getString("sub_id"));
			subject.setName(rs.getString("name"));
			subject.setDescription(rs.getString("description"));
			subject.setTestCount(rs.getString("test_count"));
			subject.setTotalTime(rs.getString("total_time"));
			subject.setTotalScore(rs.getString("total_score"));
			subject.setStatus(rs.getString("status"));
			return subject;
		}
	}

	@Override
	public List<Subject> findAllSubjects() {
		List<Subject> subjects = jdbcTemplate.query(FIND_ALL_SUBJECT, new SubjectMapper());
		return subjects;
	}

	@Override
	public Subject getSubjectById(String subId) {
		List<Subject> subjects = jdbcTemplate.query(FIND_BY_ID, new Object[]{subId}, new SubjectMapper());
		if(subjects.size() > 0){
			return subjects.get(0);
		}else{
			return null;
		}
	}

	@Override
	public Integer editSubject(Subject subject) throws Exception {
		if(subject != null && getSubjectById(subject.getSubId()) != null){
			return jdbcTemplate.update(FIND_EDIT_SUBJECT, new Object[]{subject.getSubId(), subject.getName(), subject.getDescription(), subject.getTestCount(), subject.getTotalTime(), subject.getTotalScore(), subject.getStatus(), subject.getSubId()});
		}else{
			throw new Exception("Update Subject Fail!");
		}
	}

	@Override
	public Integer createSubject(Subject subject) throws Exception {
		if(subject != null && getSubjectById(subject.getSubId()) == null){
			return jdbcTemplate.update(FIND_CREATE_SUBJECT, new Object[]{subject.getSubId(), subject.getName(), subject.getDescription(), subject.getTestCount(), subject.getTotalTime(), subject.getTotalScore(), subject.getStatus()});
		}else{
			throw new Exception("Create User Fail!");
		}
	}

	@Override
	public Integer deleteSubject(String subId) throws Exception {
		if(getSubjectById(subId) != null){
			return jdbcTemplate.update(FIND_DELETE_SUBJECT, new Object[]{subId});
		}else{
			throw new Exception("Delete Subject Fail!");
		}
	}
	*/
}
